package com.im.util;

import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;

import com.im.service.dto.PlanProject;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * excel导出工具
 * 
 * @author xuwenbo
 *
 */
public class ExcelUtil {

	/**
	 * 设置标题样式
	 * 
	 * @param workbook
	 * @return
	 * @author xuwenbo
	 */
	private static HSSFCellStyle getHeaderStyle(HSSFWorkbook workbook) {
		HSSFCellStyle headerStyle = workbook.createCellStyle(); // 标题样式
		headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中
		headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

		HSSFFont headerFont = workbook.createFont(); // 标题字体
		headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 字体加粗
		headerFont.setFontHeightInPoints((short) 11);
		headerStyle.setFont(headerFont);

		return headerStyle;
	}
	
	/**
     * 生成excel文件
     * @param filename
     * @param workbook
     * @author xuwenbo
	 * @throws IOException 
     */
    public static void createExcelFile(String filename,HSSFWorkbook workbook) throws IOException{
        FileOutputStream fos = new FileOutputStream(filename);
        workbook.write(fos);
        fos.flush();
        fos.close();
    }

    /**
     * 浏览器下载excel
     * @param filename
     * @param workbook
     * @param response
     * @throws Exception
     * @author xuwenbo
     */
    public static void downloadExcelFile(String filename,HSSFWorkbook workbook,HttpServletResponse response) throws IOException{
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(filename, "utf-8"));
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }
	
	/**
	 * 单元格信息
	 * @param text
	 * @param style
	 * @param row
	 * @param col
	 * @param sheet
	 * @author xuwenbo
	 */
	private static void cell(String text, CellStyle style, int row, int col, HSSFSheet sheet) {
    	HSSFRow sheetRow = sheet.getRow(row);
        if (sheetRow == null) {
            sheetRow = sheet.createRow(row);
        }
        HSSFCell cell = sheetRow.getCell(col);
        if (cell == null) {
            cell = sheetRow.createCell(col);
        }
        cell.setCellStyle(style);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(text);
    }

	/**
	 * 导出规划工程信息
	 * 
	 * @param list
	 * @return
	 * @author xuwenbo
	 */
	public static HSSFWorkbook obtianPlanProjWorkbook(List<PlanProject> list) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFCellStyle headerStyle = getHeaderStyle(workbook);
		HSSFSheet sheet = workbook.createSheet("规划工程信息");

		// 设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
		sheet.setColumnWidth(1, 15 * 256);
		sheet.setColumnWidth(8, 15 * 256);
		// 合并单元格
		sheet.addMergedRegion(new CellRangeAddress(0,1,0,0));//序号
		sheet.addMergedRegion(new CellRangeAddress(0,1,1,1));//工程名称
		sheet.addMergedRegion(new CellRangeAddress(0,1,2,2));//所属规划
		sheet.addMergedRegion(new CellRangeAddress(0,1,3,3));//建设单位
		sheet.addMergedRegion(new CellRangeAddress(0,0,4,5));//建设年限
		sheet.addMergedRegion(new CellRangeAddress(0,1,6,6));//工程负责人
		sheet.addMergedRegion(new CellRangeAddress(0,1,7,7));//工程联系人
		sheet.addMergedRegion(new CellRangeAddress(0,1,8,8));//主要建设内容
		sheet.addMergedRegion(new CellRangeAddress(0,0,9,14));//阶段资金构成
		sheet.addMergedRegion(new CellRangeAddress(0,1,15,15));//状态
		
		int col = 0;
		cell("序号", headerStyle, 0, col++, sheet);
		cell("工程名称", headerStyle, 0, col++, sheet);
		cell("所属规划", headerStyle, 0, col++, sheet);
		cell("建设单位", headerStyle, 0, col++, sheet);
		cell("建设年限", headerStyle, 0, col++, sheet);
		cell("建设年限", headerStyle, 0, col++, sheet);
		cell("工程负责人", headerStyle, 0, col++, sheet);
		cell("工程联系人", headerStyle, 0, col++, sheet);
		cell("主要建设内容", headerStyle, 0, col++, sheet);
		cell("阶段资金构成（单位：万元）", headerStyle, 0, col++, sheet);
		cell("阶段资金构成（单位：万元）", headerStyle, 0, col++, sheet);
		cell("阶段资金构成（单位：万元）", headerStyle, 0, col++, sheet);
		cell("阶段资金构成（单位：万元）", headerStyle, 0, col++, sheet);
		cell("阶段资金构成（单位：万元）", headerStyle, 0, col++, sheet);
		cell("阶段资金构成（单位：万元）", headerStyle, 0, col++, sheet);
		cell("状态", headerStyle, 0, col++, sheet);
		
		col = 0;
		cell("序号", headerStyle, 1, col++, sheet);
		cell("工程名称", headerStyle, 1, col++, sheet);
		cell("所属规划", headerStyle, 1, col++, sheet);
		cell("建设单位", headerStyle, 1, col++, sheet);
		cell("开工年份", headerStyle, 1, col++, sheet);
		cell("完工年份", headerStyle, 1, col++, sheet);
		cell("工程负责人", headerStyle, 1, col++, sheet);
		cell("工程联系人", headerStyle, 1, col++, sheet);
		cell("主要建设内容", headerStyle, 1, col++, sheet);
		cell("合计", headerStyle, 1, col++, sheet);
		cell("部补助", headerStyle, 1, col++, sheet);
		cell("省财政", headerStyle, 1, col++, sheet);
		cell("政府融资", headerStyle, 1, col++, sheet);
		cell("市州配套", headerStyle, 1, col++, sheet);
		cell("企业自筹", headerStyle, 1, col++, sheet);
		cell("状态", headerStyle, 1, col++, sheet);

		// 内容样式
		HSSFCellStyle contentStyle = workbook.createCellStyle();
		contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

		// 设置日期格式
//		contentStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyy-m-d"));

		for (int i = 0; i < list.size(); i++) {
			PlanProject pp = list.get(i);
			col = 0;
			cell(i + 1 + "", contentStyle, i + 2, col++, sheet);
			cell(pp.getPlanProjName(), contentStyle, i + 2, col++, sheet);
			cell(pp.getPlanProjClassName(), contentStyle, i + 2, col++, sheet);
			cell(pp.getDevelopOrgName(), contentStyle, i + 2, col++, sheet);
			cell(pp.getStartDt(), contentStyle, i + 2, col++, sheet);
			cell(pp.getEndDt(), contentStyle, i + 2, col++, sheet);
			cell(pp.getLeadingPersonName(), contentStyle, i + 2, col++, sheet);
			cell(pp.getLinkmanName(), contentStyle, i + 2, col++, sheet);
			cell(pp.getDescription(), contentStyle, i + 2, col++, sheet);
			cell(pp.getTotal().toString(), contentStyle, i + 2, col++, sheet);
			cell(pp.getFundSourceBu() == null ? "" : pp.getFundSourceBu()+"", contentStyle, i + 2, col++, sheet);
			cell(pp.getFundSourceSheng() == null ? "" : pp.getFundSourceSheng() + "", contentStyle, i + 2, col++, sheet);
			cell(pp.getFundSourceGao() == null ? "" : pp.getFundSourceGao() + "", contentStyle, i + 2, col++, sheet);
			cell(pp.getFundSourceShi() == null ? "" : pp.getFundSourceShi() + "", contentStyle, i + 2, col++, sheet);
			cell(pp.getFundSourceQi() == null ? "" : pp.getFundSourceQi() + "", contentStyle, i + 2, col++, sheet);
			cell(pp.getStatusName(), contentStyle, i + 2, col++, sheet);
		}
		return workbook;
	}

//-------文件导入

	public static List<List<String>> readXlsx(String path) throws IOException {
		InputStream input = new FileInputStream(path);
		return readXlsx(input);
	}

	public static List<List<String>> readXls(String path) throws IOException {
		InputStream input = new FileInputStream(path);
		return readXls(input);
	}

	public static List<List<String>> readXlsx(InputStream input) throws IOException {
		List<List<String>> result = new ArrayList<List<String>>();
		XSSFWorkbook workbook = new XSSFWorkbook(input);
		for (XSSFSheet xssfSheet : workbook) {
			if (xssfSheet == null) {
				continue;
			}
			for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
				XSSFRow row = xssfSheet.getRow(rowNum);
				int minCellNum = row.getFirstCellNum();
				int maxCellNum = row.getLastCellNum();
				List<String> rowList = new ArrayList<String>();
				for (int i = minCellNum; i < maxCellNum; i++) {
					XSSFCell cell = row.getCell(i);
					if (cell == null) {
						continue;
					}
					rowList.add(cell.toString());
				}
				result.add(rowList);
			}
		}
		return result;
	}

	public static List<List<String>> readXls(InputStream input) throws IOException {
		List<List<String>> result = new ArrayList<List<String>>();
		HSSFWorkbook workbook = new HSSFWorkbook(input);
		for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
			HSSFSheet sheet = workbook.getSheetAt(numSheet);
			if (sheet == null) {
				continue;
			}
			for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
				HSSFRow row = sheet.getRow(rowNum);
				int minCellNum = row.getFirstCellNum();
				int maxCellNum = row.getLastCellNum();
				List<String> rowList = new ArrayList<String>();
				for (int i = minCellNum; i < maxCellNum; i++) {
					HSSFCell cell = row.getCell(i);
					if (cell == null) {
						continue;
					}
					rowList.add(getStringVal(cell));
				}
				result.add(rowList);
			}
		}
		return result;
	}

	private static String getStringVal(HSSFCell cell) {
		switch (cell.getCellType()) {
			case Cell.CELL_TYPE_BOOLEAN:
				return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
			case Cell.CELL_TYPE_FORMULA:
				return cell.getCellFormula();
			case Cell.CELL_TYPE_NUMERIC:
				cell.setCellType(Cell.CELL_TYPE_STRING);
				return cell.getStringCellValue();
			case Cell.CELL_TYPE_STRING:
				return cell.getStringCellValue();
			default:
				return null;
		}
	}

}
